//package com.surfilter.sqlopera.job;
//
//import org.slf4j.Logger;
//import org.slf4j.LoggerFactory;
//import org.springframework.jdbc.core.JdbcTemplate;
//
//import java.sql.Connection;
//import java.sql.PreparedStatement;
//import java.sql.SQLException;
//import java.text.ParseException;
//import java.text.SimpleDateFormat;
//import java.util.Calendar;
//import java.util.Date;
//import java.util.List;
//import java.util.Map;
//
///**
// * 互联网金融统计
// * Created by jstarseven on 2017/5/9.
// */
//public class HJJob {
//    private static Logger logger = LoggerFactory.getLogger(HJJob.class);
//    private JdbcTemplate jdbcTemplate; // 数据入库
//    private static String dataBaseName = "funds_task_tag";
//
//    public HJJob(JdbcTemplate jdbcTemplate) {
//        this.jdbcTemplate = jdbcTemplate;
//    }
//
//    public void HJJobCount() throws ParseException {
//        final String rtj_sql = " (\n" +
//                "\tSELECT\n" +
//                "\t\t15 AS tjlx,\n" +
//                "\t\t ? AS tjsj,\n" +
//                "\t\tifnull(aa.sid, 0) sid,\n" +
//                "\t\tifnull(aa.dsid, 0) dsid,\n" +
//                "\t\tifnull(aa.qid, 0) qid,\n" +
//                "\t\tifnull(aa.jdid, 0) jdid,\n" +
//                "\t\tifnull(aa.zl, 0) zl,\n" +
//                "\t\tifnull(bb.rzl, 0) rzl,\n" +
//                "\t\tifnull(cc.zzl, 0) zzl,\n" +
//                "\t\tifnull(dd.yzl, 0) yzl\n" +
//                "\tFROM\n" +
//                "\t\t(\n" +
//                "\t\t\tSELECT\n" +
//                "\t\t\t\tifnull(jg.sid, 0) sid,\n" +
//                "\t\t\t\tifnull(jg.dsid, 0) dsid,\n" +
//                "\t\t\t\tifnull(jg.qid, 0) qid,\n" +
//                "\t\t\t\tifnull(jg.jdid, 0) jdid,\n" +
//                "\t\t\t\tcount(ch.ptid) zl\n" +
//                "\t\t\tFROM\n" +
//                "\t\t\t\t(\n" +
//                "\t\t\t\t\tSELECT\n" +
//                "\t\t\t\t\t\tptid,\n" +
//                "\t\t\t\t\t\tcyjg\n" +
//                "\t\t\t\t\tFROM\n" +
//                "\t\t\t\t\t\tpt\n" +
//                "\t\t\t\t\tWHERE\n" +
//                "\t\t\t\t\t\tsczt = 0\n" +
//                "\t\t\t\t\tAND sxsj IS NOT NULL\n" +
//                "\t\t\t\t\tAND sxsj != ''\n" +
//                "\t\t\t\t) ch\n" +
//                "\t\t\tLEFT JOIN jg ON ch.cyjg = jg.jgid\n" +
//                "\t\t\tGROUP BY\n" +
//                "\t\t\t\tjg.sid,\n" +
//                "\t\t\t\tjg.dsid,\n" +
//                "\t\t\t\tjg.qid,\n" +
//                "\t\t\t\tjg.jdid\n" +
//                "\t\t) aa\n" +
//                "\tLEFT JOIN (\n" +
//                "\t\tSELECT\n" +
//                "\t\t\tifnull(jg.sid, 0) sid,\n" +
//                "\t\t\tifnull(jg.dsid, 0) dsid,\n" +
//                "\t\t\tifnull(jg.qid, 0) qid,\n" +
//                "\t\t\tifnull(jg.jdid, 0) jdid,\n" +
//                "\t\t\tcount(ch.ptid) rzl\n" +
//                "\t\tFROM\n" +
//                "\t\t\t(\n" +
//                "\t\t\t\tSELECT\n" +
//                "\t\t\t\t\tptid,\n" +
//                "\t\t\t\t\tcyjg\n" +
//                "\t\t\t\tFROM\n" +
//                "\t\t\t\t\tpt\n" +
//                "\t\t\t\tWHERE\n" +
//                "\t\t\t\t\tsczt = 0\n" +
//                "\t\t\t\tAND sxsj BETWEEN  ? and ?\n" +
//                "\t\t\t\tAND sxsj IS NOT NULL\n" +
//                "\t\t\t\tAND sxsj != ''\n" +
//                "\t\t\t) ch\n" +
//                "\t\tLEFT JOIN jg ON ch.cyjg = jg.jgid\n" +
//                "\t\tGROUP BY\n" +
//                "\t\t\tjg.sid,\n" +
//                "\t\t\tjg.dsid,\n" +
//                "\t\t\tjg.qid,\n" +
//                "\t\t\tjg.jdid\n" +
//                "\t) bb ON aa.sid = bb.sid\n" +
//                "\tAND aa.dsid = bb.dsid\n" +
//                "\tAND aa.qid = bb.qid\n" +
//                "\tAND aa.jdid = bb.jdid\n" +
//                "\tLEFT JOIN (\n" +
//                "\t\tSELECT\n" +
//                "\t\t\tifnull(jg.sid, 0) sid,\n" +
//                "\t\t\tifnull(jg.dsid, 0) dsid,\n" +
//                "\t\t\tifnull(jg.qid, 0) qid,\n" +
//                "\t\t\tifnull(jg.jdid, 0) jdid,\n" +
//                "\t\t\tcount(ch.ptid) zzl\n" +
//                "\t\tFROM\n" +
//                "\t\t\t(\n" +
//                "\t\t\t\tSELECT\n" +
//                "\t\t\t\t\tptid,\n" +
//                "\t\t\t\t\tcyjg\n" +
//                "\t\t\t\tFROM\n" +
//                "\t\t\t\t\tpt\n" +
//                "\t\t\t\tWHERE\n" +
//                "\t\t\t\t\tsczt = 0\n" +
//                "\t\t\t\tAND sxsj BETWEEN  ? and ?\n" +
//                "\t\t\t\tAND sxsj IS NOT NULL\n" +
//                "\t\t\t\tAND sxsj != ''\n" +
//                "\t\t\t) ch\n" +
//                "\t\tLEFT JOIN jg ON ch.cyjg = jg.jgid\n" +
//                "\t\tGROUP BY\n" +
//                "\t\t\tjg.sid,\n" +
//                "\t\t\tjg.dsid,\n" +
//                "\t\t\tjg.qid,\n" +
//                "\t\t\tjg.jdid\n" +
//                "\t) cc ON aa.sid = cc.sid\n" +
//                "\tAND aa.dsid = cc.dsid\n" +
//                "\tAND aa.qid = cc.qid\n" +
//                "\tAND aa.jdid = cc.jdid\n" +
//                "\tLEFT JOIN (\n" +
//                "\t\tSELECT\n" +
//                "\t\t\tifnull(jg.sid, 0) sid,\n" +
//                "\t\t\tifnull(jg.dsid, 0) dsid,\n" +
//                "\t\t\tifnull(jg.qid, 0) qid,\n" +
//                "\t\t\tifnull(jg.jdid, 0) jdid,\n" +
//                "\t\t\tcount(ch.ptid) yzl\n" +
//                "\t\tFROM\n" +
//                "\t\t\t(\n" +
//                "\t\t\t\tSELECT\n" +
//                "\t\t\t\t\tptid,\n" +
//                "\t\t\t\t\tcyjg\n" +
//                "\t\t\t\tFROM\n" +
//                "\t\t\t\t\tpt\n" +
//                "\t\t\t\tWHERE\n" +
//                "\t\t\t\t\tsczt = 0\n" +
//                "\t\t\t\tAND sxsj BETWEEN  ? and ?\n" +
//                "\t\t\t\tAND sxsj IS NOT NULL\n" +
//                "\t\t\t\tAND sxsj != ''\n" +
//                "\t\t\t) ch\n" +
//                "\t\tLEFT JOIN jg ON ch.cyjg = jg.jgid\n" +
//                "\t\tGROUP BY\n" +
//                "\t\t\tjg.sid,\n" +
//                "\t\t\tjg.dsid,\n" +
//                "\t\t\tjg.qid,\n" +
//                "\t\t\tjg.jdid\n" +
//                "\t) dd ON aa.sid = dd.sid\n" +
//                "\tAND aa.dsid = dd.dsid\n" +
//                "\tAND aa.qid = dd.qid\n" +
//                "\tAND aa.jdid = dd.jdid\n" +
//                ")\n" +
//                "UNION ALL\n" +
//                "\t(\n" +
//                "\t\tSELECT\n" +
//                "\t\t\t16 AS tjlx,\n" +
//                "\t\t\t ? AS tjsj,\n" +
//                "\t\t\tifnull(aa.sid, 0) sid,\n" +
//                "\t\t\tifnull(aa.dsid, 0) dsid,\n" +
//                "\t\t\tifnull(aa.qid, 0) qid,\n" +
//                "\t\t\tifnull(aa.jdid, 0) jdid,\n" +
//                "\t\t\tifnull(aa.zl, 0) zl,\n" +
//                "\t\t\tifnull(bb.rzl, 0) rzl,\n" +
//                "\t\t\tifnull(cc.zzl, 0) zzl,\n" +
//                "\t\t\tifnull(dd.yzl, 0) yzl\n" +
//                "\t\tFROM\n" +
//                "\t\t\t(\n" +
//                "\t\t\t\tSELECT\n" +
//                "\t\t\t\t\tifnull(jg.sid, 0) sid,\n" +
//                "\t\t\t\t\tifnull(jg.dsid, 0) dsid,\n" +
//                "\t\t\t\t\tifnull(jg.qid, 0) qid,\n" +
//                "\t\t\t\t\tifnull(jg.jdid, 0) jdid,\n" +
//                "\t\t\t\t\tcount(ch.ptid) zl\n" +
//                "\t\t\t\tFROM\n" +
//                "\t\t\t\t\t(\n" +
//                "\t\t\t\t\t\tSELECT\n" +
//                "\t\t\t\t\t\t\tptid,\n" +
//                "\t\t\t\t\t\t\tcyjg\n" +
//                "\t\t\t\t\t\tFROM\n" +
//                "\t\t\t\t\t\t\tpt\n" +
//                "\t\t\t\t\t\tWHERE\n" +
//                "\t\t\t\t\t\t\tsczt = 0\n" +
//                "\t\t\t\t\t\tAND xxsj IS NOT NULL\n" +
//                "\t\t\t\t\t\tAND xxsj != ''\n" +
//                "\t\t\t\t\t) ch\n" +
//                "\t\t\t\tLEFT JOIN jg ON ch.cyjg = jg.jgid\n" +
//                "\t\t\t\tGROUP BY\n" +
//                "\t\t\t\t\tjg.sid,\n" +
//                "\t\t\t\t\tjg.dsid,\n" +
//                "\t\t\t\t\tjg.qid,\n" +
//                "\t\t\t\t\tjg.jdid\n" +
//                "\t\t\t) aa\n" +
//                "\t\tLEFT JOIN (\n" +
//                "\t\t\tSELECT\n" +
//                "\t\t\t\tifnull(jg.sid, 0) sid,\n" +
//                "\t\t\t\tifnull(jg.dsid, 0) dsid,\n" +
//                "\t\t\t\tifnull(jg.qid, 0) qid,\n" +
//                "\t\t\t\tifnull(jg.jdid, 0) jdid,\n" +
//                "\t\t\t\tcount(ch.ptid) rzl\n" +
//                "\t\t\tFROM\n" +
//                "\t\t\t\t(\n" +
//                "\t\t\t\t\tSELECT\n" +
//                "\t\t\t\t\t\tptid,\n" +
//                "\t\t\t\t\t\tcyjg\n" +
//                "\t\t\t\t\tFROM\n" +
//                "\t\t\t\t\t\tpt\n" +
//                "\t\t\t\t\tWHERE\n" +
//                "\t\t\t\t\t\tsczt = 0\n" +
//                "\t\t\t\t\tAND xxsj BETWEEN  ? and ?\n" +
//                "\t\t\t\t\tAND xxsj IS NOT NULL\n" +
//                "\t\t\t\t\tAND xxsj != ''\n" +
//                "\t\t\t\t) ch\n" +
//                "\t\t\tLEFT JOIN jg ON ch.cyjg = jg.jgid\n" +
//                "\t\t\tGROUP BY\n" +
//                "\t\t\t\tjg.sid,\n" +
//                "\t\t\t\tjg.dsid,\n" +
//                "\t\t\t\tjg.qid,\n" +
//                "\t\t\t\tjg.jdid\n" +
//                "\t\t) bb ON aa.sid = bb.sid\n" +
//                "\t\tAND aa.dsid = bb.dsid\n" +
//                "\t\tAND aa.qid = bb.qid\n" +
//                "\t\tAND aa.jdid = bb.jdid\n" +
//                "\t\tLEFT JOIN (\n" +
//                "\t\t\tSELECT\n" +
//                "\t\t\t\tifnull(jg.sid, 0) sid,\n" +
//                "\t\t\t\tifnull(jg.dsid, 0) dsid,\n" +
//                "\t\t\t\tifnull(jg.qid, 0) qid,\n" +
//                "\t\t\t\tifnull(jg.jdid, 0) jdid,\n" +
//                "\t\t\t\tcount(ch.ptid) zzl\n" +
//                "\t\t\tFROM\n" +
//                "\t\t\t\t(\n" +
//                "\t\t\t\t\tSELECT\n" +
//                "\t\t\t\t\t\tptid,\n" +
//                "\t\t\t\t\t\tcyjg\n" +
//                "\t\t\t\t\tFROM\n" +
//                "\t\t\t\t\t\tpt\n" +
//                "\t\t\t\t\tWHERE\n" +
//                "\t\t\t\t\t\tsczt = 0\n" +
//                "\t\t\t\t\tAND xxsj BETWEEN  ? and ?\n" +
//                "\t\t\t\t\tAND xxsj IS NOT NULL\n" +
//                "\t\t\t\t\tAND xxsj != ''\n" +
//                "\t\t\t\t) ch\n" +
//                "\t\t\tLEFT JOIN jg ON ch.cyjg = jg.jgid\n" +
//                "\t\t\tGROUP BY\n" +
//                "\t\t\t\tjg.sid,\n" +
//                "\t\t\t\tjg.dsid,\n" +
//                "\t\t\t\tjg.qid,\n" +
//                "\t\t\t\tjg.jdid\n" +
//                "\t\t) cc ON aa.sid = cc.sid\n" +
//                "\t\tAND aa.dsid = cc.dsid\n" +
//                "\t\tAND aa.qid = cc.qid\n" +
//                "\t\tAND aa.jdid = cc.jdid\n" +
//                "\t\tLEFT JOIN (\n" +
//                "\t\t\tSELECT\n" +
//                "\t\t\t\tifnull(jg.sid, 0) sid,\n" +
//                "\t\t\t\tifnull(jg.dsid, 0) dsid,\n" +
//                "\t\t\t\tifnull(jg.qid, 0) qid,\n" +
//                "\t\t\t\tifnull(jg.jdid, 0) jdid,\n" +
//                "\t\t\t\tcount(ch.ptid) yzl\n" +
//                "\t\t\tFROM\n" +
//                "\t\t\t\t(\n" +
//                "\t\t\t\t\tSELECT\n" +
//                "\t\t\t\t\t\tptid,\n" +
//                "\t\t\t\t\t\tcyjg\n" +
//                "\t\t\t\t\tFROM\n" +
//                "\t\t\t\t\t\tpt\n" +
//                "\t\t\t\t\tWHERE\n" +
//                "\t\t\t\t\t\tsczt = 0\n" +
//                "\t\t\t\t\tAND xxsj BETWEEN  ? and ?\n" +
//                "\t\t\t\t\tAND xxsj IS NOT NULL\n" +
//                "\t\t\t\t\tAND xxsj != ''\n" +
//                "\t\t\t\t) ch\n" +
//                "\t\t\tLEFT JOIN jg ON ch.cyjg = jg.jgid\n" +
//                "\t\t\tGROUP BY\n" +
//                "\t\t\t\tjg.sid,\n" +
//                "\t\t\t\tjg.dsid,\n" +
//                "\t\t\t\tjg.qid,\n" +
//                "\t\t\t\tjg.jdid\n" +
//                "\t\t) dd ON aa.sid = dd.sid\n" +
//                "\t\tAND aa.dsid = dd.dsid\n" +
//                "\t\tAND aa.qid = dd.qid\n" +
//                "\t\tAND aa.jdid = dd.jdid\n" +
//                "\t)\n";
//        final String ztj_sql = " (\n" +
//                "\tSELECT\n" +
//                "\t\t15 AS tjlx,\n" +
//                "\t\t ? AS tjsj,\n" +
//                "\t\tifnull(aa.sid, 0) sid,\n" +
//                "\t\tifnull(aa.dsid, 0) dsid,\n" +
//                "\t\tifnull(aa.qid, 0) qid,\n" +
//                "\t\tifnull(aa.jdid, 0) jdid,\n" +
//                "\t\tifnull(aa.zl, 0) zl,\n" +
//                "\t\tifnull(cc.zzl, 0) zzl\n" +
//                "\tFROM\n" +
//                "\t\t(\n" +
//                "\t\t\tSELECT\n" +
//                "\t\t\t\tifnull(jg.sid, 0) sid,\n" +
//                "\t\t\t\tifnull(jg.dsid, 0) dsid,\n" +
//                "\t\t\t\tifnull(jg.qid, 0) qid,\n" +
//                "\t\t\t\tifnull(jg.jdid, 0) jdid,\n" +
//                "\t\t\t\tcount(ch.ptid) zl\n" +
//                "\t\t\tFROM\n" +
//                "\t\t\t\t(\n" +
//                "\t\t\t\t\tSELECT\n" +
//                "\t\t\t\t\t\tptid,\n" +
//                "\t\t\t\t\t\tcyjg\n" +
//                "\t\t\t\t\tFROM\n" +
//                "\t\t\t\t\t\tpt\n" +
//                "\t\t\t\t\tWHERE\n" +
//                "\t\t\t\t\t\tsczt = 0\n" +
//                "\t\t\t\t\tAND sxsj IS NOT NULL\n" +
//                "\t\t\t\t\tAND sxsj != ''\n" +
//                "\t\t\t\t) ch\n" +
//                "\t\t\tLEFT JOIN jg ON ch.cyjg = jg.jgid\n" +
//                "\t\t\tGROUP BY\n" +
//                "\t\t\t\tjg.sid,\n" +
//                "\t\t\t\tjg.dsid,\n" +
//                "\t\t\t\tjg.qid,\n" +
//                "\t\t\t\tjg.jdid\n" +
//                "\t\t) aa\n" +
//                "\tLEFT JOIN (\n" +
//                "\t\tSELECT\n" +
//                "\t\t\tifnull(jg.sid, 0) sid,\n" +
//                "\t\t\tifnull(jg.dsid, 0) dsid,\n" +
//                "\t\t\tifnull(jg.qid, 0) qid,\n" +
//                "\t\t\tifnull(jg.jdid, 0) jdid,\n" +
//                "\t\t\tcount(ch.ptid) zzl\n" +
//                "\t\tFROM\n" +
//                "\t\t\t(\n" +
//                "\t\t\t\tSELECT\n" +
//                "\t\t\t\t\tptid,\n" +
//                "\t\t\t\t\tcyjg\n" +
//                "\t\t\t\tFROM\n" +
//                "\t\t\t\t\tpt\n" +
//                "\t\t\t\tWHERE\n" +
//                "\t\t\t\t\tsczt = 0\n" +
//                "\t\t\t\tAND sxsj BETWEEN ? and ?\n" +
//                "\t\t\t\tAND sxsj IS NOT NULL\n" +
//                "\t\t\t\tAND sxsj != ''\n" +
//                "\t\t\t) ch\n" +
//                "\t\tLEFT JOIN jg ON ch.cyjg = jg.jgid\n" +
//                "\t\tGROUP BY\n" +
//                "\t\t\tjg.sid,\n" +
//                "\t\t\tjg.dsid,\n" +
//                "\t\t\tjg.qid,\n" +
//                "\t\t\tjg.jdid\n" +
//                "\t) cc ON aa.sid = cc.sid\n" +
//                "\tAND aa.dsid = cc.dsid\n" +
//                "\tAND aa.qid = cc.qid\n" +
//                "\tAND aa.jdid = cc.jdid\n" +
//                ")\n" +
//                "UNION ALL\n" +
//                "\t(\n" +
//                "\t\tSELECT\n" +
//                "\t\t\t16 AS tjlx,\n" +
//                "\t\t\t ? AS tjsj,\n" +
//                "\t\t\tifnull(aa.sid, 0) sid,\n" +
//                "\t\t\tifnull(aa.dsid, 0) dsid,\n" +
//                "\t\t\tifnull(aa.qid, 0) qid,\n" +
//                "\t\t\tifnull(aa.jdid, 0) jdid,\n" +
//                "\t\t\tifnull(aa.zl, 0) zl,\n" +
//                "\t\t\tifnull(cc.zzl, 0) zzl\n" +
//                "\t\tFROM\n" +
//                "\t\t\t(\n" +
//                "\t\t\t\tSELECT\n" +
//                "\t\t\t\t\tifnull(jg.sid, 0) sid,\n" +
//                "\t\t\t\t\tifnull(jg.dsid, 0) dsid,\n" +
//                "\t\t\t\t\tifnull(jg.qid, 0) qid,\n" +
//                "\t\t\t\t\tifnull(jg.jdid, 0) jdid,\n" +
//                "\t\t\t\t\tcount(ch.ptid) zl\n" +
//                "\t\t\t\tFROM\n" +
//                "\t\t\t\t\t(\n" +
//                "\t\t\t\t\t\tSELECT\n" +
//                "\t\t\t\t\t\t\tptid,\n" +
//                "\t\t\t\t\t\t\tcyjg\n" +
//                "\t\t\t\t\t\tFROM\n" +
//                "\t\t\t\t\t\t\tpt\n" +
//                "\t\t\t\t\t\tWHERE\n" +
//                "\t\t\t\t\t\t\tsczt = 0\n" +
//                "\t\t\t\t\t\tAND xxsj IS NOT NULL\n" +
//                "\t\t\t\t\t\tAND xxsj != ''\n" +
//                "\t\t\t\t\t) ch\n" +
//                "\t\t\t\tLEFT JOIN jg ON ch.cyjg = jg.jgid\n" +
//                "\t\t\t\tGROUP BY\n" +
//                "\t\t\t\t\tjg.sid,\n" +
//                "\t\t\t\t\tjg.dsid,\n" +
//                "\t\t\t\t\tjg.qid,\n" +
//                "\t\t\t\t\tjg.jdid\n" +
//                "\t\t\t) aa\n" +
//                "\t\tLEFT JOIN (\n" +
//                "\t\t\tSELECT\n" +
//                "\t\t\t\tifnull(jg.sid, 0) sid,\n" +
//                "\t\t\t\tifnull(jg.dsid, 0) dsid,\n" +
//                "\t\t\t\tifnull(jg.qid, 0) qid,\n" +
//                "\t\t\t\tifnull(jg.jdid, 0) jdid,\n" +
//                "\t\t\t\tcount(ch.ptid) zzl\n" +
//                "\t\t\tFROM\n" +
//                "\t\t\t\t(\n" +
//                "\t\t\t\t\tSELECT\n" +
//                "\t\t\t\t\t\tptid,\n" +
//                "\t\t\t\t\t\tcyjg\n" +
//                "\t\t\t\t\tFROM\n" +
//                "\t\t\t\t\t\tpt\n" +
//                "\t\t\t\t\tWHERE\n" +
//                "\t\t\t\t\t\tsczt = 0\n" +
//                "\t\t\t\t\tAND xxsj BETWEEN ? and ?\n" +
//                "\t\t\t\t\tAND xxsj IS NOT NULL\n" +
//                "\t\t\t\t\tAND xxsj != ''\n" +
//                "\t\t\t\t) ch\n" +
//                "\t\t\tLEFT JOIN jg ON ch.cyjg = jg.jgid\n" +
//                "\t\t\tGROUP BY\n" +
//                "\t\t\t\tjg.sid,\n" +
//                "\t\t\t\tjg.dsid,\n" +
//                "\t\t\t\tjg.qid,\n" +
//                "\t\t\t\tjg.jdid\n" +
//                "\t\t) cc ON aa.sid = cc.sid\n" +
//                "\t\tAND aa.dsid = cc.dsid\n" +
//                "\t\tAND aa.qid = cc.qid\n" +
//                "\t\tAND aa.jdid = cc.jdid\n" +
//                "\t)\n";
//        final String ytj_sql = " (\n" +
//                "\tSELECT\n" +
//                "\t\t15 AS tjlx,\n" +
//                "\t\t ? AS tjsj,\n" +
//                "\t\tifnull(aa.sid, 0) sid,\n" +
//                "\t\tifnull(aa.dsid, 0) dsid,\n" +
//                "\t\tifnull(aa.qid, 0) qid,\n" +
//                "\t\tifnull(aa.jdid, 0) jdid,\n" +
//                "\t\tifnull(aa.zl, 0) zl,\n" +
//                "\t\tifnull(cc.yzl, 0) yzl\n" +
//                "\tFROM\n" +
//                "\t\t(\n" +
//                "\t\t\tSELECT\n" +
//                "\t\t\t\tifnull(jg.sid, 0) sid,\n" +
//                "\t\t\t\tifnull(jg.dsid, 0) dsid,\n" +
//                "\t\t\t\tifnull(jg.qid, 0) qid,\n" +
//                "\t\t\t\tifnull(jg.jdid, 0) jdid,\n" +
//                "\t\t\t\tcount(ch.ptid) zl\n" +
//                "\t\t\tFROM\n" +
//                "\t\t\t\t(\n" +
//                "\t\t\t\t\tSELECT\n" +
//                "\t\t\t\t\t\tptid,\n" +
//                "\t\t\t\t\t\tcyjg\n" +
//                "\t\t\t\t\tFROM\n" +
//                "\t\t\t\t\t\tpt\n" +
//                "\t\t\t\t\tWHERE\n" +
//                "\t\t\t\t\t\tsczt = 0\n" +
//                "\t\t\t\t\tAND sxsj IS NOT NULL\n" +
//                "\t\t\t\t\tAND sxsj != ''\n" +
//                "\t\t\t\t) ch\n" +
//                "\t\t\tLEFT JOIN jg ON ch.cyjg = jg.jgid\n" +
//                "\t\t\tGROUP BY\n" +
//                "\t\t\t\tjg.sid,\n" +
//                "\t\t\t\tjg.dsid,\n" +
//                "\t\t\t\tjg.qid,\n" +
//                "\t\t\t\tjg.jdid\n" +
//                "\t\t) aa\n" +
//                "\tLEFT JOIN (\n" +
//                "\t\tSELECT\n" +
//                "\t\t\tifnull(jg.sid, 0) sid,\n" +
//                "\t\t\tifnull(jg.dsid, 0) dsid,\n" +
//                "\t\t\tifnull(jg.qid, 0) qid,\n" +
//                "\t\t\tifnull(jg.jdid, 0) jdid,\n" +
//                "\t\t\tcount(ch.ptid) yzl\n" +
//                "\t\tFROM\n" +
//                "\t\t\t(\n" +
//                "\t\t\t\tSELECT\n" +
//                "\t\t\t\t\tptid,\n" +
//                "\t\t\t\t\tcyjg\n" +
//                "\t\t\t\tFROM\n" +
//                "\t\t\t\t\tpt\n" +
//                "\t\t\t\tWHERE\n" +
//                "\t\t\t\t\tsczt = 0\n" +
//                "\t\t\t\tAND sxsj BETWEEN ? and ?\n" +
//                "\t\t\t\tAND sxsj IS NOT NULL\n" +
//                "\t\t\t\tAND sxsj != ''\n" +
//                "\t\t\t) ch\n" +
//                "\t\tLEFT JOIN jg ON ch.cyjg = jg.jgid\n" +
//                "\t\tGROUP BY\n" +
//                "\t\t\tjg.sid,\n" +
//                "\t\t\tjg.dsid,\n" +
//                "\t\t\tjg.qid,\n" +
//                "\t\t\tjg.jdid\n" +
//                "\t) cc ON aa.sid = cc.sid\n" +
//                "\tAND aa.dsid = cc.dsid\n" +
//                "\tAND aa.qid = cc.qid\n" +
//                "\tAND aa.jdid = cc.jdid\n" +
//                ")\n" +
//                "UNION ALL\n" +
//                "\t(\n" +
//                "\t\tSELECT\n" +
//                "\t\t\t16 AS tjlx,\n" +
//                "\t\t\t ? AS tjsj,\n" +
//                "\t\t\tifnull(aa.sid, 0) sid,\n" +
//                "\t\t\tifnull(aa.dsid, 0) dsid,\n" +
//                "\t\t\tifnull(aa.qid, 0) qid,\n" +
//                "\t\t\tifnull(aa.jdid, 0) jdid,\n" +
//                "\t\t\tifnull(aa.zl, 0) zl,\n" +
//                "\t\t\tifnull(cc.yzl, 0) yzl\n" +
//                "\t\tFROM\n" +
//                "\t\t\t(\n" +
//                "\t\t\t\tSELECT\n" +
//                "\t\t\t\t\tifnull(jg.sid, 0) sid,\n" +
//                "\t\t\t\t\tifnull(jg.dsid, 0) dsid,\n" +
//                "\t\t\t\t\tifnull(jg.qid, 0) qid,\n" +
//                "\t\t\t\t\tifnull(jg.jdid, 0) jdid,\n" +
//                "\t\t\t\t\tcount(ch.ptid) zl\n" +
//                "\t\t\t\tFROM\n" +
//                "\t\t\t\t\t(\n" +
//                "\t\t\t\t\t\tSELECT\n" +
//                "\t\t\t\t\t\t\tptid,\n" +
//                "\t\t\t\t\t\t\tcyjg\n" +
//                "\t\t\t\t\t\tFROM\n" +
//                "\t\t\t\t\t\t\tpt\n" +
//                "\t\t\t\t\t\tWHERE\n" +
//                "\t\t\t\t\t\t\tsczt = 0\n" +
//                "\t\t\t\t\t\tAND xxsj IS NOT NULL\n" +
//                "\t\t\t\t\t\tAND xxsj != ''\n" +
//                "\t\t\t\t\t) ch\n" +
//                "\t\t\t\tLEFT JOIN jg ON ch.cyjg = jg.jgid\n" +
//                "\t\t\t\tGROUP BY\n" +
//                "\t\t\t\t\tjg.sid,\n" +
//                "\t\t\t\t\tjg.dsid,\n" +
//                "\t\t\t\t\tjg.qid,\n" +
//                "\t\t\t\t\tjg.jdid\n" +
//                "\t\t\t) aa\n" +
//                "\t\tLEFT JOIN (\n" +
//                "\t\t\tSELECT\n" +
//                "\t\t\t\tifnull(jg.sid, 0) sid,\n" +
//                "\t\t\t\tifnull(jg.dsid, 0) dsid,\n" +
//                "\t\t\t\tifnull(jg.qid, 0) qid,\n" +
//                "\t\t\t\tifnull(jg.jdid, 0) jdid,\n" +
//                "\t\t\t\tcount(ch.ptid) yzl\n" +
//                "\t\t\tFROM\n" +
//                "\t\t\t\t(\n" +
//                "\t\t\t\t\tSELECT\n" +
//                "\t\t\t\t\t\tptid,\n" +
//                "\t\t\t\t\t\tcyjg\n" +
//                "\t\t\t\t\tFROM\n" +
//                "\t\t\t\t\t\tpt\n" +
//                "\t\t\t\t\tWHERE\n" +
//                "\t\t\t\t\t\tsczt = 0\n" +
//                "\t\t\t\t\tAND xxsj BETWEEN ? and ?\n" +
//                "\t\t\t\t\tAND xxsj IS NOT NULL\n" +
//                "\t\t\t\t\tAND xxsj != ''\n" +
//                "\t\t\t\t) ch\n" +
//                "\t\t\tLEFT JOIN jg ON ch.cyjg = jg.jgid\n" +
//                "\t\t\tGROUP BY\n" +
//                "\t\t\t\tjg.sid,\n" +
//                "\t\t\t\tjg.dsid,\n" +
//                "\t\t\t\tjg.qid,\n" +
//                "\t\t\t\tjg.jdid\n" +
//                "\t\t) cc ON aa.sid = cc.sid\n" +
//                "\t\tAND aa.dsid = cc.dsid\n" +
//                "\t\tAND aa.qid = cc.qid\n" +
//                "\t\tAND aa.jdid = cc.jdid\n" +
//                "\t)\n" +
//                "\n" +
//                "\n";
//
////        Date xxsj = JdbcUtilsSing.getMinTime(jdbcTemplate, "pt", "xxsj");
////        Date sxsj = JdbcUtilsSing.getMinTime(jdbcTemplate, "pt", "sxsj");
//        final Date min_time = new SimpleDateFormat("yyyy-MM-dd").parse("2010-03-03");
//        //日统计历史
//        new Thread(new Runnable() {
//            @Override
//            public void run() {
////                Calendar ca = Calendar.getInstance();
////                Date cycle_cur_time = new Date();
////                while (min_time.getTime() <= cycle_cur_time.getTime()) {
////                    //当前时间
////                    ca.setTime(cycle_cur_time);
////                    //当前时间前一天
////                    ca.add(Calendar.DATE, -1);// 日期减1
////                    Date bf_one_day = ca.getTime();
////                    //当前时间前一周
////                    ca.add(Calendar.WEEK_OF_MONTH, -1);// 周减1
////                    Date bf_one_week = ca.getTime();
////                    //当前时间前一月
//////                    ca.add(Calendar.MONTH, -1);// 月减1
//////                    Date bf_one_month = ca.getTime();
////                    Date bf_one_month = TimeUtil.getLast30Day(cycle_cur_time);
////                    //查询入库
////                    List<Map<String, Object>> maps = jdbcTemplate.queryForList(rtj_sql,cycle_cur_time, bf_one_day, cycle_cur_time, bf_one_week, cycle_cur_time, bf_one_month,
////                            cycle_cur_time,cycle_cur_time, bf_one_day, cycle_cur_time, bf_one_week, cycle_cur_time, bf_one_month, cycle_cur_time);
////                    cycle_cur_time = bf_one_day;
////                    if (null != maps && !maps.isEmpty()) {
////                        RTJ_INTO_MySql("dyfb_rtj", maps);
////                    }
////                }
//            }
//        }).start();
//
//        //周统计历史
//        new Thread(new Runnable() {
//            @Override
//            public void run() {
//                Calendar ca = Calendar.getInstance();
//                Date cycle_cur_time = new Date();
//                while (min_time.getTime() <= cycle_cur_time.getTime()) {
//                    //当前时间
//                    ca.setTime(cycle_cur_time);
//                    //11.3 -> 10.26
//                    ca.add(Calendar.WEEK_OF_MONTH, -1);// 周减1
//                    Date bf_one_week = ca.getTime();
//                    //当前时间前一周的周一和周日
//                    Date first_week = TimeUtil.getFirstOfWeek(bf_one_week);
//                    Date last_week = TimeUtil.getLastOfWeek(bf_one_week);
//                    Date tjsj = TimeUtil.getNextday(last_week);
//                    //查询入库
//                    List<Map<String, Object>> maps = jdbcTemplate.queryForList(ztj_sql,tjsj, first_week, last_week, tjsj, first_week, last_week);
//                    cycle_cur_time = bf_one_week;
//                    if (null != maps && !maps.isEmpty()) {
//                        ZTJ_INTO_MySql("dyfb_ztj", maps);
//                    }
//                }
//            }
//        }).start();
//
//        //月统计历史
//        new Thread(new Runnable() {
//            @Override
//            public void run() {
//                Calendar ca = Calendar.getInstance();
//                Date cycle_cur_time = new Date();
//                while (min_time.getTime() <= cycle_cur_time.getTime()) {
//                    //当前时间
//                    ca.setTime(cycle_cur_time);
//                    //11.3 -> 10.2
//                    ca.add(Calendar.MONTH, -1);// 月减1
//                    Date bf_one_month = ca.getTime();
//                    //当前时间前一个月的月初和月末
//                    Date first_month = TimeUtil.getFirstDayOfMonth(bf_one_month);
//                    Date last_month = TimeUtil.getLastDayOfMonth(bf_one_month);
//                    Date tjsj = TimeUtil.getNextday(last_month);
//                    //查询入库
//                    List<Map<String, Object>> maps = jdbcTemplate.queryForList(ytj_sql,tjsj, first_month, last_month, tjsj, first_month, last_month);
//                    cycle_cur_time = bf_one_month;
//                    if (null != maps && !maps.isEmpty()) {
//                        YTJ_INTO_MySql("dyfb_ytj", maps);
//                    }
//                }
//            }
//        }).start();
//    }
//
//
//    /**
//     * 日统计
//     * jdbc批量插入mysql
//     *
//     * @param list
//     */
//    public void RTJ_INTO_MySql(String table_name, List<Map<String, Object>> list) {
//        logger.info("[" + table_name + "]地域分布上线消亡日统计:新增" + list.size() + "条记录");
//        long start_time = new Date().getTime(), item_start_time = new Date().getTime();
//        String sql = "INSERT INTO `" +dataBaseName+ "`.`dyfb_rtj` " +
//                "(`tjsj`, `tjlx`,`sid`,`dsid`,`qid`," +
//                " `jdid`, `zl`," +
//                " `rzl`, `zzl`, `yzl`) " +
//                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
//        try {
//            Connection conn = JdbcUtilsSing.getConnection();
//            conn.setAutoCommit(false);
//            PreparedStatement pst = conn.prepareStatement(sql);
//            for (int i = 0; i < list.size(); i++) {
//                pst.setObject(1, list.get(i).get("tjsj"));
//                pst.setObject(2, list.get(i).get("tjlx"));
//                pst.setObject(3, list.get(i).get("sid"));
//                pst.setObject(4, list.get(i).get("dsid"));
//                pst.setObject(5, list.get(i).get("qid"));
//                pst.setObject(6, list.get(i).get("jdid"));
//                pst.setObject(7, list.get(i).get("zl"));
//                pst.setObject(8, list.get(i).get("rzl"));
//                pst.setObject(9, list.get(i).get("zzl"));
//                pst.setObject(10, list.get(i).get("yzl"));
//                pst.addBatch();
//                if ((i + 1) % 5000 == 0) {
//                    pst.executeBatch();
//                    conn.commit();
//                    long item_end_time = new Date().getTime();
//                    logger.info("[" + table_name + "]地域分布上线消亡日统计:单次插入5000条记录,耗时: " + (item_end_time - item_start_time) + "ms");
//                    item_start_time = new Date().getTime();
//                }
//            }
//            pst.executeBatch();
//            conn.commit();
//            pst.close();
//            JdbcUtilsSing.freePool(conn);
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        long end_time = new Date().getTime();
//        logger.info("[" + table_name + "]地域分布上线消亡日统计:批处理插入" + list.size() + "条记录,耗时: " + (end_time - start_time) + "ms");
//    }
//
//
//    /**
//     * 周统计
//     * jdbc批量插入mysql
//     *
//     * @param list
//     */
//    public void ZTJ_INTO_MySql(String table_name, List<Map<String, Object>> list) {
//        logger.info("[" + table_name + "]地域分布上线消亡周统计:新增" + list.size() + "条记录");
//        long start_time = new Date().getTime(), item_start_time = new Date().getTime();
//        String sql = "INSERT INTO `" +dataBaseName+ "`.`dyfb_ztj` " +
//                "(`tjsj`, `tjlx`,`sid`,`dsid`,`qid`," +
//                " `jdid`, `zl`, `zzl`) " +
//                "VALUES (?, ?, ?, ?, ?, ?, ?, ?);";
//        try {
//            Connection conn = JdbcUtilsSing.getConnection();
//            conn.setAutoCommit(false);
//            PreparedStatement pst = conn.prepareStatement(sql);
//            for (int i = 0; i < list.size(); i++) {
//                pst.setObject(1, list.get(i).get("tjsj"));
//                pst.setObject(2, list.get(i).get("tjlx"));
//                pst.setObject(3, list.get(i).get("sid"));
//                pst.setObject(4, list.get(i).get("dsid"));
//                pst.setObject(5, list.get(i).get("qid"));
//                pst.setObject(6, list.get(i).get("jdid"));
//                pst.setObject(7, list.get(i).get("zl"));
//                pst.setObject(8, list.get(i).get("zzl"));
//                pst.addBatch();
//                if ((i + 1) % 5000 == 0) {
//                    pst.executeBatch();
//                    conn.commit();
//                    long item_end_time = new Date().getTime();
//                    logger.info("[" + table_name + "]地域分布上线消亡周统计:单次插入5000条记录,耗时: " + (item_end_time - item_start_time) + "ms");
//                    item_start_time = new Date().getTime();
//                }
//            }
//            pst.executeBatch();
//            conn.commit();
//            pst.close();
//            JdbcUtilsSing.freePool(conn);
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        long end_time = new Date().getTime();
//        logger.info("[" + table_name + "]地域分布上线消亡周统计:批处理插入" + list.size() + "条记录,耗时: " + (end_time - start_time) + "ms");
//    }
//
//    /**
//     * 月统计
//     * jdbc批量插入mysql
//     *
//     * @param list
//     */
//    public void YTJ_INTO_MySql(String table_name, List<Map<String, Object>> list) {
//        logger.info("[" + table_name + "]地域分布上线消亡月统计:新增" + list.size() + "条记录");
//        long start_time = new Date().getTime(), item_start_time = new Date().getTime();
//        String sql = "INSERT INTO `" +dataBaseName+ "`.`dyfb_ytj` " +
//                "(`tjsj`, `tjlx`,`sid`,`dsid`,`qid`," +
//                " `jdid`, `zl`, `yzl`) " +
//                "VALUES (?, ?, ?, ?, ?, ?, ?, ?);";
//        try {
//            Connection conn = JdbcUtilsSing.getConnection();
//            conn.setAutoCommit(false);
//            PreparedStatement pst = conn.prepareStatement(sql);
//            for (int i = 0; i < list.size(); i++) {
//                pst.setObject(1, list.get(i).get("tjsj"));
//                pst.setObject(2, list.get(i).get("tjlx"));
//                pst.setObject(3, list.get(i).get("sid"));
//                pst.setObject(4, list.get(i).get("dsid"));
//                pst.setObject(5, list.get(i).get("qid"));
//                pst.setObject(6, list.get(i).get("jdid"));
//                pst.setObject(7, list.get(i).get("zl"));
//                pst.setObject(8, list.get(i).get("yzl"));
//                pst.addBatch();
//                if ((i + 1) % 5000 == 0) {
//                    pst.executeBatch();
//                    conn.commit();
//                    long item_end_time = new Date().getTime();
//                    logger.info("[" + table_name + "]地域分布上线消亡月统计:单次插入5000条记录,耗时: " + (item_end_time - item_start_time) + "ms");
//                    item_start_time = new Date().getTime();
//                }
//            }
//            pst.executeBatch();
//            conn.commit();
//            pst.close();
//            JdbcUtilsSing.freePool(conn);
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        long end_time = new Date().getTime();
//        logger.info("[" + table_name + "]地域分布上线消亡月统计:批处理插入" + list.size() + "条记录,耗时: " + (end_time - start_time) + "ms");
//    }
//}
